import pymysql
from DBUtils.PooledDB import PooledDB
from config.Config import Config
from Libs.logs.logs import get_logger
import time
from contextlib import contextmanager

logger = get_logger(__name__)


class MySQLConnectionPool:
    def __init__(self):
        self.pool = None
        self._init_pool()
    
    def _init_pool(self):
        """初始化连接池"""
        try:
            self.pool = PooledDB(
                creator=pymysql,
                mincached=5,  # 减少初始连接数
                maxcached=20,  # 最大缓存连接数
                maxconnections=100,  # 减少最大连接数
                blocking=True,
                host=Config.host,
                port=Config.port,
                user=Config.user,
                password=Config.password,
                database=Config.database,
                charset='utf8mb4',
                autocommit=False,  # 显式控制事务
                ping=1,  # 连接前ping检查
                max_allowed_packet=16*1024*1024  # 16MB
            )
            logger.info("数据库连接池初始化成功")
        except Exception as e:
            logger.error(f"数据库连接池初始化失败: {e}")
            raise

    @contextmanager
    def get_connection(self):
        """获取数据库连接的上下文管理器"""
        conn = None
        cursor = None
        try:
            conn = self.pool.connection()
            cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
            yield conn, cursor
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"数据库操作异常: {e}")
            raise
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    def select_one(self, sql, *args):
        """查询单条数据"""
        try:
            with self.get_connection() as (conn, cursor):
                cursor.execute(sql, args)
                result = cursor.fetchone()
                return result
        except Exception as e:
            logger.error(f"查询单条数据失败: {sql}, 参数: {args}, 错误: {e}")
            raise

    def select_all(self, sql, *args):
        """查询多条数据"""
        try:
            with self.get_connection() as (conn, cursor):
                cursor.execute(sql, args)
                result = cursor.fetchall()
                return result
        except Exception as e:
            logger.error(f"查询多条数据失败: {sql}, 参数: {args}, 错误: {e}")
            raise

    def insert_one(self, sql, args):
        """插入单条数据"""
        try:
            with self.get_connection() as (conn, cursor):
                cursor.execute(sql, args)
                conn.commit()
                return {'result': True, 'id': int(cursor.lastrowid)}
        except Exception as e:
            logger.error(f"插入单条数据失败: {sql}, 参数: {args}, 错误: {e}")
            return {'result': False, 'err': str(e)}

    def insert_all(self, sql, datas):
        """插入多条批量插入"""
        try:
            with self.get_connection() as (conn, cursor):
                cursor.executemany(sql, datas)
                conn.commit()
                return {'result': True, 'id': int(cursor.lastrowid)}
        except Exception as e:
            logger.error(f"批量插入数据失败: {sql}, 错误: {e}")
            return {'result': False, 'err': str(e)}

    def update_one(self, sql, args):
        """更新数据"""
        try:
            with self.get_connection() as (conn, cursor):
                cursor.execute(sql, args)
                conn.commit()
                return {'result': True, 'affected_rows': cursor.rowcount}
        except Exception as e:
            logger.error(f"更新数据失败: {sql}, 参数: {args}, 错误: {e}")
            return {'result': False, 'err': str(e)}

    def delete_one(self, sql, *args):
        """删除数据"""
        try:
            with self.get_connection() as (conn, cursor):
                cursor.execute(sql, args)
                conn.commit()
                return {'result': True, 'affected_rows': cursor.rowcount}
        except Exception as e:
            logger.error(f"删除数据失败: {sql}, 参数: {args}, 错误: {e}")
            return {'result': False, 'err': str(e)}

    def execute_transaction(self, operations):
        """
        执行事务操作
        operations: [(sql, args), (sql, args), ...]
        """
        try:
            with self.get_connection() as (conn, cursor):
                for sql, args in operations:
                    cursor.execute(sql, args)
                conn.commit()
                return {'result': True}
        except Exception as e:
            logger.error(f"事务执行失败: {e}")
            return {'result': False, 'err': str(e)}

    def test_connection(self):
        """测试数据库连接"""
        try:
            with self.get_connection() as (conn, cursor):
                cursor.execute("SELECT 1")
                return True
        except Exception as e:
            logger.error(f"数据库连接测试失败: {e}")
            return False

if Config.DATABASE_CONNECT_STATUS:
    mysql_db = MySQLConnectionPool()
else:
    logger.info("配置为不连接数据库！")
    mysql_db = None


if __name__ == '__main__':
    pass
    # """
    # CREATE TABLE `names` (
    #   `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
    #   `name` VARCHAR(30) DEFAULT NULL COMMENT '姓名',
    #   `sex` VARCHAR(20) DEFAULT NULL COMMENT '性别',
    #   `age` int(5) DEFAULT NULL COMMENT '年龄',
    #   PRIMARY KEY (`id`) USING BTREE
    # ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='数据导入mysql';
    #
    # """
    # mysql = MySQLConnectionPool()
    # sql_select_one = "SELECT DATE_FORMAT(t.acctstarttime,'%%Y-%%m-%%d') as date, count(0) as num " \
    #                  "FROM raddhcplog_2023_07 t " \
    #                  "where DATE_FORMAT(t.acctstarttime,'%%Y-%%m-%%d') = '2023-07-14'"
    # results = mysql.select_one(sql_select_one)
    # print(results)

    # sql_insert_one = "insert into `names` (`name`, sex, age) values (%s,%s,%s)"
    # mysql.insert_one(sql_insert_one, ('唐三', '男', 25))
    #
    # datas = [
    #     ('戴沐白', '男', 26),
    #     ('奥斯卡', '男', 26),
    #     ('唐三', '男', 25),
    #     ('小舞', '女', 100000),
    #     ('马红俊', '男', 23),
    #     ('宁荣荣', '女', 22),
    #     ('朱竹清', '女', 21),
    # ]
    # sql_insert_all = "insert into `names` (`name`, sex, age) values (%s,%s,%s)"
    # mysql.insert_all(sql_insert_all, datas)
    #
    # sql_update_one = "update `names` set age=%s where `name`=%s"
    # mysql.update_one(sql_update_one, (28, '唐三'))
    #
    # sql_delete_one = 'delete from `names` where `name`=%s '
    # mysql.delete_one(sql_delete_one, ('唐三',))
    #
    # sql_select_one = 'select * from `names` where `name`=%s'
    # results = mysql.select_one(sql_select_one, ('唐三',))
    # print(results)
    #
    # sql_select_all = 'select * from `names` where `name`=%s'
    # results = mysql.select_all(sql_select_all, ('唐三',))
    # print(results)
